<?php
namespace app\manage\controller;
use think\Db;
use think\helper\Time;

class Bindex extends Base
{
    //收支汇总
    public function moneyCollect(){
        $times = input('times','');
        $pro_id = input('pro_id','');

        $map = [];
        $map1 = [];
        $map2 = [];


        if(!empty($pro_id)){
            $map['a.pro_id']=$pro_id;
            $map1['a.pro_id']=$pro_id;
        }
        if($times != ''){
            $time_arr = explode(' - ',$times);
            $map['a.time'] = ['between',[$time_arr[0],$time_arr[1]]];

            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
        }

        $clueTimeArr=model('ClueCostLog')->alias('a')->field('time')
            ->where($map)
            ->group('time')
           ->select();
        $timeArr=array_column($clueTimeArr,'time');
        if($timeArr){
//            $map1['days']=['in',$timeArr];
//            $map1['cdays']='2023-11-20';
//            $map2="FROM_UNIXTIME(a.create_time, '%Y-%m-%d') = '2023-11-20'";
            $timeStr=implode("','",$timeArr);
            $map2="FROM_UNIXTIME(a.create_time, '%Y-%m-%d') IN ('".$timeStr."')";
//            $map2="FROM_UNIXTIME(a.create_time, '%Y-%m-%d') = '2023-11-20'";
        }

        $infos=model('ClueCostLog')->alias('a')->field('pro_id,time,sum(money) money,sum(true_consume) true_consume')
            ->where($map)
            ->group('time,a.pro_id')
            ->order('time desc')
            ->paginate();

//        dump($map1);die;

        $clue=model('Clue')->alias('a')->field("count(a.id) num,sum(IF(a.status=1,1,0)) push_num,sum(IF(a.status=1,b.price,0)) push_price")
            ->join('clue_log b','b.clue_id=a.id')
            ->where($map1)
            ->where($map2)
            ->find();
//        dump($map1);
//        dump($map2);
//        dump($clue);die;

        $total=model('ClueCostLog')->alias('a')->field('sum(money) money,sum(true_consume) true_consume')
            ->where($map)
            ->find();


        $products = model('Product')->where('status',1)->column('name','id');
        $productArr = Db::name('product')->column('name','id');

        foreach ($infos as &$v) {
            if(!empty($v['pro_id'])){
                $v['pro_name']=$productArr[$v['pro_id']];
            }else{
                $v['pro_name']='未知';
            }
            $v['days']=$v['time'];
            $stime=strtotime($v['time']);
            $etime=strtotime($v['time'].' 23:59:59');
            $clueTime=model('Clue')->alias('a')->field("count(a.id) num,sum(IF(a.status=1,1,0)) push_num,sum(IF(a.status=1,b.price,0)) push_price,a.pro_id")
                ->join('clue_log b','b.clue_id=a.id','LEFT')
                ->where('a.pro_id',$v['pro_id'])->whereTime('a.create_time', 'between', [$stime, $etime])->find();
            $v['num']=!empty($clueTime['num'])?$clueTime['num']:0;
            $v['push_num']=!empty($clueTime['push_num'])?$clueTime['push_num']:0;
            $v['push_price']=!empty($clueTime['push_price'])?$clueTime['push_price']:0;
            $v['lirun']=bcsub($v['push_price'],$v['true_consume'],2);
            if($v['push_price']>0){
                $v['lirun_rate']=bcdiv($v['lirun'],$v['push_price'],4)*100;
            }else{
                $v['lirun_rate']=0;
            }
        }


//        $bulidSql =model('Clue')->alias('a')->field("FROM_UNIXTIME(a.create_time, '%Y-%m-%d') as days,count(a.id) num,sum(IF(a.status=1,1,0)) push_num,sum(IF(a.status=1,b.price,0)) push_price,a.pro_id")
//            ->join('clue_log b','b.clue_id=a.id','LEFT')
//            ->where($map1)
//            ->group('days,a.pro_id')
//            ->order('days desc')
//            ->buildSql();
//
//        $infos= Db::table($bulidSql)->alias("spl")->fetchSql(false)->paginate();
//
//        $clue=model('Clue')->alias('a')->field('count(a.id) num,sum(IF(a.status=1,1,0)) push_num,sum(IF(a.status=1,b.price,0)) push_price')
//            ->join('clue_log b','b.clue_id=a.id','LEFT')
//            ->where($map1)
//            ->find();
//        $total=model('ClueCostLog')->alias('a')->field('sum(money) money,sum(true_consume) true_consume')
//            ->where($map)
//            ->find();
//
//        $list=$infos->all();
//
//        $products = model('Product')->where('status',1)->column('name','id');
//        $productArr = Db::name('product')->column('name','id');
//
//        foreach ($list as &$v) {
//
//            if(!empty($v['pro_id'])){
//                $v['pro_name']=$productArr[$v['pro_id']];
//            }else{
//                $v['pro_name']='未知';
//            }
//
//            $daoru=model('ClueCostLog')->alias('a')->field('sum(money) money,sum(true_consume) true_consume')
//                ->where('time',$v['days'])
//                ->where('pro_id',$v['pro_id'])
//                ->find();
//            if(is_null($daoru['money'])){
//                $v['money']='-';
//            }else{
//                $v['money']=$daoru['money'];
//            }
//
//            if(is_null($daoru['true_consume'])){
//                $v['true_consume']='-';
//                $v['lirun']='-';
//                $v['lirun_rate']='-';
//            }else{
//                $v['true_consume']=$daoru['true_consume'];
//                $v['lirun']=bcsub($v['push_price'],$v['true_consume'],2);
//                if($v['push_price']>0){
//                    $v['lirun_rate']=bcdiv($v['lirun'],$v['push_price'],4)*100;
//                }else{
//                    $v['lirun_rate']=0;
//                }
//            }
//
//        }
//
        $money=!empty($total['money'])?$total['money']:0;
        $true_consume=!empty($total['true_consume'])?$total['true_consume']:0;
        $num=!empty($clue['num'])?$clue['num']:0;
        $push_num=!empty($clue['push_num'])?$clue['push_num']:0;
        $push_price=!empty($clue['push_price'])?$clue['push_price']:0;

        $lirun=bcsub($push_price,$true_consume,2);
        if($push_price>0){
            $lirun_rate=bcdiv($lirun,$push_price,4)*100;
        }else{
            $lirun_rate=0;
        }


        return $this->fetch('',[

            'infos' => $infos,
            'list' => $infos,
            'times' => $times,
            'money' => $money,
            'true_consume' => $true_consume,
            'num' => $num,
            'push_num' => $push_num,
            'push_price' => $push_price,
            'lirun' => $lirun,
            'lirun_rate' => $lirun_rate,
            'products' => $products,
            'pro_id' => $pro_id,

        ]);
    }

    //城市收支汇总
    public function moneyCollectCity(){
        $times = input('times','');
        $pro_id = input('pro_id','');
        $cityname = input('cityname','');
        $lirunw = input('lirunw','');

        $map = [];
        $map1 = [];
        $map2 = [];

        if(!empty($pro_id)){
            $map['a.pro_id']=$pro_id;
            $map1['a.pro_id']=$pro_id;
        }
        if($times != ''){
            $time_arr = explode(' - ',$times);
            $map['a.time'] = ['between',[$time_arr[0],$time_arr[1]]];

            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
        }

        //模糊查询城市
        if(!empty($cityname)){
            $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
            if($cityIdArr){
                $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
                $map1['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
            }else{
                $map1['a.city']=0;
                $map['a.city']=0;
            }
        }


        $clueTimeArr=model('ClueCostLog')->alias('a')->field('time')
            ->where($map)
            ->group('time')
            ->select();
        $timeArr=array_column($clueTimeArr,'time');
        if($timeArr){
//            $map1['days']=['in',$timeArr];
//            $map1['cdays']='2023-11-20';
//            $map2="FROM_UNIXTIME(a.create_time, '%Y-%m-%d') = '2023-11-20'";
            $timeStr=implode("','",$timeArr);
            $map2="FROM_UNIXTIME(a.create_time, '%Y-%m-%d') IN ('".$timeStr."')";
//            $map2="FROM_UNIXTIME(a.create_time, '%Y-%m-%d') = '2023-11-20'";
        }


        $infos=model('ClueCostLog')->alias('a')->field('pro_id,city,time,sum(money) money,sum(true_consume) true_consume')
            ->where($map)
            ->group('time,a.pro_id,city')
            ->order('time desc')
            ->paginate();

        $clue=model('Clue')->alias('a')->field('count(a.id) num,sum(IF(a.status=1,1,0)) push_num,sum(IF(a.status=1,b.price,0)) push_price')
            ->join('clue_log b','b.clue_id=a.id','LEFT')
            ->where($map1)
            ->where($map2)
            ->find();
        $total=model('ClueCostLog')->alias('a')->field('sum(money) money,sum(true_consume) true_consume')
            ->where($map)
            ->find();


        $products = model('Product')->where('status',1)->column('name','id');
        $productArr = Db::name('product')->column('name','id');

        foreach ($infos as &$v) {
            if($v['city'] != ''){
                $ids = explode('-',$v['city']);
                $cityNum=count($ids);
                if($cityNum==1){
                    $v['city_name'] = $this->getCityName($ids[0]);
                }elseif ($cityNum==2){
                    $prov = $this->getCityName($ids[0]);
                    $city = $this->getCityName($ids[1]);

                    $v['city_name'] = $prov.'-'.$city;

                }
            }else{
                $v['city_name'] = '';
            }
            if(!empty($v['pro_id'])){
                $v['pro_name']=$productArr[$v['pro_id']];
            }else{
                $v['pro_name']='未知';
            }
            $v['days']=$v['time'];
            $stime=strtotime($v['time']);
            $etime=strtotime($v['time'].' 23:59:59');
            $clueTime=model('Clue')->alias('a')->field("count(a.id) num,sum(IF(a.status=1,1,0)) push_num,sum(IF(a.status=1,b.price,0)) push_price,a.pro_id")
                ->join('clue_log b','b.clue_id=a.id','LEFT')
                ->where('a.pro_id',$v['pro_id'])
                ->where('a.city',$v['city'])
                ->whereTime('a.create_time', 'between', [$stime, $etime])->find();
            $v['num']=!empty($clueTime['num'])?$clueTime['num']:0;
            $v['push_num']=!empty($clueTime['push_num'])?$clueTime['push_num']:0;
            $v['push_price']=!empty($clueTime['push_price'])?$clueTime['push_price']:0;
            $v['lirun']=bcsub($v['push_price'],$v['true_consume'],2);
            if($v['lirun']<=$lirunw){
                $v['lirun']='<span style="color: red">'.$v['lirun'].'</span>';
            }
            if($v['push_price']>0){
                $v['lirun_rate']=bcdiv($v['lirun'],$v['push_price'],4)*100;
            }else{
                $v['lirun_rate']=0;
            }
        }


//        if(!empty($pro_id)){
//            $map['a.pro_id']=$pro_id;
//            $map1['a.pro_id']=$pro_id;
//        }
//        if($times != ''){
//            $time_arr = explode(' - ',$times);
//            $map['a.time'] = ['between',[$time_arr[0],$time_arr[1]]];
//
//            $map1['a.create_time'] = ['between',[strtotime($time_arr[0]),strtotime($time_arr[1].' 23:59:59')]];
//        }
//
//        //模糊查询城市
//        if(!empty($cityname)){
//            $cityIdArr=Db::name('city')->field('id,type,pid')->where('name','like',$cityname."%")->where('type',2)->find();
//            if($cityIdArr){
//                $map['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
//                $map1['a.city']=$cityIdArr['pid'].'-'.$cityIdArr['id'];
//            }else{
//                $map1['a.city']=0;
//                $map['a.city']=0;
//            }
//        }
//
//
//
//        $bulidSql =model('Clue')->alias('a')->field("a.city,FROM_UNIXTIME(a.create_time, '%Y-%m-%d') as days,count(a.id) num,sum(IF(a.status=1,1,0)) push_num,sum(IF(a.status=1,b.price,0)) push_price,a.pro_id")
//            ->join('clue_log b','b.clue_id=a.id','LEFT')
//            ->where($map1)
//            ->group('days,a.pro_id,a.city')
//            ->order('days desc')
//            ->buildSql();
//
//        $infos= Db::table($bulidSql)->alias("spl")->fetchSql(false)->paginate();
//
//        $clue=model('Clue')->alias('a')->field('count(a.id) num,sum(IF(a.status=1,1,0)) push_num,sum(IF(a.status=1,b.price,0)) push_price')
//            ->join('clue_log b','b.clue_id=a.id','LEFT')
//            ->where($map1)
//            ->find();
//        $total=model('ClueCostLog')->alias('a')->field('sum(money) money,sum(true_consume) true_consume')
//            ->where($map)
//            ->find();
//
//        $list=$infos->all();
//
//        $products = model('Product')->where('status',1)->column('name','id');
//        $productArr = Db::name('product')->column('name','id');
//
//        foreach ($list as &$v) {
//
//            if($v['city'] != ''){
//                $ids = explode('-',$v['city']);
//                $cityNum=count($ids);
//                if($cityNum==1){
//                    $v['city_name'] = $this->getCityName($ids[0]);
//                }elseif ($cityNum==2){
//                    $prov = $this->getCityName($ids[0]);
//                    $city = $this->getCityName($ids[1]);
//
//                    $v['city_name'] = $prov.'-'.$city;
//
//                }
//            }else{
//                $v['city_name'] = '';
//            }
//
//            if(!empty($v['pro_id'])){
//                $v['pro_name']=$productArr[$v['pro_id']];
//            }else{
//                $v['pro_name']='未知';
//            }
//
//            $daoru=model('ClueCostLog')->alias('a')->field('sum(money) money,sum(true_consume) true_consume')
//                ->where('time',$v['days'])
//                ->where('pro_id',$v['pro_id'])
//                ->where('city',$v['city'])
//                ->find();
//            if(is_null($daoru['money'])){
//                $v['money']='-';
//            }else{
//                $v['money']=$daoru['money'];
//            }
//
//            if(is_null($daoru['true_consume'])){
//                $v['true_consume']='-';
//                $v['lirun']='-';
//                $v['lirun_rate']='-';
//            }else{
//                $v['true_consume']=$daoru['true_consume'];
//                $v['lirun']=bcsub($v['push_price'],$v['true_consume'],2);
//                if($v['push_price']>0){
//                    $v['lirun_rate']=bcdiv($v['lirun'],$v['push_price'],4)*100;
//                }else{
//                    $v['lirun_rate']=0;
//                }
//            }
//
//        }

        $money=!empty($total['money'])?$total['money']:0;
        $true_consume=!empty($total['true_consume'])?$total['true_consume']:0;
        $num=!empty($clue['num'])?$clue['num']:0;
        $push_num=!empty($clue['push_num'])?$clue['push_num']:0;
        $push_price=!empty($clue['push_price'])?$clue['push_price']:0;
        if(is_null($total['true_consume'])){
            $lirun='-';
            $lirun_rate='-';
            $money='-';
            $true_consume='-';
        }else{
            $lirun=bcsub($push_price,$true_consume,2);
            if($push_price>0){
                $lirun_rate=bcdiv($lirun,$push_price,4)*100;
            }else{
                $lirun_rate=0;
            }
        }

        return $this->fetch('',[

            'infos' => $infos,
            'list' => $infos,
            'times' => $times,
            'money' => $money,
            'true_consume' => $true_consume,
            'num' => $num,
            'push_num' => $push_num,
            'push_price' => $push_price,
            'lirun' => $lirun,
            'lirun_rate' => $lirun_rate,
            'products' => $products,
            'pro_id' => $pro_id,
            'cityname' => $cityname,
            'lirunw' => $lirunw,

        ]);
    }
}